#!/usr/bin/python
# -*- coding:UTF-8 -*-

from plan_util import *


class funds_ranking(Util):
    def __init__(self, host='', username='', password='', media='', pthost="", charset='utf8'):
        self.dbconn = None
        self.cursor = None

        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.media = media
        self.pthost = pthost
        self.flag = True
        self.list = []
        self.size = 400
        self.paymentType = {
            2: u"定金",
            3: u"补款",
            4: u"退款",
            5: u"补款退款",
            6: u"劵退款",
            7: u"定金退款"
        }
        self.advanceType = {
            0: u"None",
            1: u"合同预付款",
            2: u"工程预付款",
            3: u"主材预付款",
            4: u"正林预付款"
        }

        self.colum = u"""
        s.orderId AS `订单号`,
        s.no AS `销售单号`,
        CONCAT(cv.NAME, ' ', h.houseNumber) AS `客户地址`,
        cc.NAME AS `客户名`,
        cu.userId AS `渠道人员ID`,
        cu.currentName AS `渠道`,
        cd.NAME AS `部门`,
        rd.NAME AS `分区`,
        s.category AS `品类`,
        s.brand AS `品牌`,
        s.total AS `销售单总价`,
        s.coupon AS `销售券值`,
        s.couponInfo AS `销售券`,
        sr.srid AS `SRID`,
        sr.cash AS `现金`,
        sr.cashInfo AS `现金信息`,
        sr.bank AS `刷卡`,
        sr.bankInfo AS `刷卡信息`,
        sr.checkv AS `转账`,
        sr.checkvBank AS `转账银行`,
        sr.installment AS `分期`,
        sr.installmentBank AS `分期银行`,
        sr.accounts AS `对公`,
        sr.accountsBank AS `对公银行`,
        sr.advance AS `预付款`,
        sr.advanceType AS `预付款类型`,
        sr.coupon AS `券值`,
        sr.couponInfo AS `券信息`,
        sp.type AS `收款类型`,
        sp.referMoney AS `应交金额`,
        cuid.currentName AS `创建人`,
        sr.collecterName AS `收款人`,
        sr.collectTime AS `收款时间`,
        sr.confirmTime AS `确认时间`,
        sr.COMMENT AS `备注`
        """

        self.SQL_Sales_Funds = u"""
            SELECT
                %s
            FROM
                   sales.sales AS s ,
                   payment.salespayment AS sp ,
                   payment.salesreceipt AS sr ,
                   communion.house AS h ,
                   communion.village AS cv,
                   communion.customer AS cc ,
                   communion.`user` AS cu ,
                   communion.`user` AS cuid,
                   communion.department AS cd ,
                   communion.department AS rd
            WHERE
                      s.sid = sp.sid 
                   AND sp.srid = sr.srid 
                   AND cc.cid = s.cid
                   AND s.hid = h.hid
                   AND h.vid = cv.vid
                   AND s.suid = cu.uid
                   AND cd.fdid = rd.did
                   AND s.did = cd.dId
                   AND sp.cuid = cuid.uid
                   AND sr.collectTime >= '%s 00:00:00'
                   AND sr.collectTime <= '%s 23:59:59' 
                   %s
                """

    def company(self, cloumCompany, company):
        if company != 0:
            companyId = u"AND %s = %s" % (cloumCompany, company)
        else:
            companyId = u" "

        return companyId

    def main(self, pid, args, comapny):

        try:
            self.getDBCursor(self.host, self.username, self.password, self.charset)

            startTime, endTime = args.split("-")

            falgs = self.controlTime(startTime, endTime)
            if falgs == False:
                raise RuntimeError(u'时间应在1-31内')

            path = tempfile.gettempdir() + u"/Funds.xls"
            sqlcount = self.SQL_Sales_Funds % ("count(*)", startTime, endTime, self.company("s.company", comapny))

            count = self.count(sqlcount)
            if count == 0:
                raise RuntimeError(u'The query result is zero!')

            all = count / 0.8
            molecular = all * 0.1
            schedule = (molecular / all) * 100

            paln = {
                u"plan.schedule": schedule,
                u"plan.status": 4
            }

            self.updataSchedule(self.pthost, pid, paln)

            sql = self.SQL_Sales_Funds % (self.colum, startTime, endTime, self.company("s.company", comapny))
            print sql

            workbook, sheet = self.createWorkbook(u"销售收款")

            tableHead = [u"SRID", u"现金", u"现金信息", u"刷卡", u"刷卡信息", u"转账", u"转账银行", u"分期", u"分期银行", u"对公", u"对公银行",
                         u"收款劵值", u"收款劵", u"预付款", u"预付款类型", u"收款人", u"收款时间", u"确认时间", u"备注", u"订单编号", u"销售编号", u"楼盘地址",
                         u"客户", u"渠道人员ID", u"渠道", u"部门", u"分区", u"品类", u"品牌", u"销售单总价", u"销售劵值", u"销售劵", u"收款类型",
                         u"收款金额",
                         u"创建人"]

            self.createTableHead(sheet, tableHead)

            count = 1
            nowPage = 1
            flag = True
            while (flag):

                data = self.page(sql, nowPage, self.size)

                for i, v in enumerate(data):
                    orderId = v[0]  # `订单号`
                    no = v[1]  # 销售单号
                    houseNumber = v[2]  # 客户地址
                    ccname = v[3]  # 客户名
                    userId = v[4]  # 渠道人员ID
                    cucurrentName = v[5]  # 渠道
                    deptname = v[6]  # 部门
                    regionname = v[7]  # 分区
                    category = v[8]  # 品类
                    brand = v[9]  # 品牌
                    total = v[10]  # 销售单总价
                    scoupon = v[11]  # 销售劵值
                    scouponInfo = v[12]  # 销售劵
                    srid = v[13]  # `SRID`,
                    cash = v[14]  # 现金
                    cashInfo = v[15]  # 现金信息
                    bank = v[16]  # 刷卡
                    bankInfo = v[17]  # 刷卡信息
                    checkv = v[18]  # 转账
                    checkvBank = v[19]  # 转账银行
                    installment = v[20]  # 分期
                    installmentBank = v[21]  # 分期银行
                    accounts = v[22]  # 对公
                    accountsBank = v[23]  # 对公银行
                    advance = v[24]  # 预付款
                    advanceType = v[25]  # 预付款类型
                    advanceTypeName = self.advanceType[advanceType]
                    coupon = v[26]  # 劵值
                    couponInfo = v[27]  # 劵信息
                    typenum = int(v[28])  # 收款类型
                    type = self.paymentType[typenum]  # 收款类型
                    referMoney = float(v[29])  # `应交金额`
                    referMoney = -referMoney if typenum in (4, 5, 6, 7) else referMoney
                    currentName = v[30]  # 创建人
                    collecterName = v[31]  # 收款人
                    collectTime = v[32]  # 收款时间
                    confirmTime = v[33]  # 确认时间
                    comment = v[34]  # 备注

                    collectTime = u"无" if not collectTime else (u"%s" % collectTime)
                    confirmTime = u"无" if not confirmTime else (u"%s" % confirmTime)

                    datas = (srid, cash, cashInfo, bank, bankInfo, checkv, checkvBank, installment, installmentBank,
                             accounts, accountsBank, coupon, couponInfo, advance, advanceTypeName, collecterName,
                             collectTime, confirmTime, comment, orderId, no, houseNumber, ccname, userId, cucurrentName,
                             deptname,
                             regionname, category, brand, total, scoupon, scouponInfo, type, referMoney, currentName)

                    self.createTableHead(sheet, datas, count)
                    count += 1

                molecular = molecular + len(data)
                schedules = (molecular / all) * 100
                paln = {
                    u"plan.schedule": int(schedules),
                    u"plan.status": 4
                }
                self.updataSchedule(self.pthost, pid, paln)

                nowPage = nowPage + 1

                if len(data) < self.size:
                    flag = False

            self.saveWorkbook(path, workbook)

            url = self.media + u"uploadOther.do"
            resultfilepath = self.uploudFile(path, url)
            self.delFile(path)

            paln = {
                u"plan.resultFilepath": resultfilepath
            }

            self.updataSchedule(self.pthost, pid, paln)

        except Exception as e:
            self.flag = False
            self.errCall(pid, self.pthost, self.media, e)

        finally:
            self.modelEnd(pid, self.pthost, self.media, self.flag)
            sys.exit(0)


if __name__ == '__main__':
    """
        导出销售款项_外链统计脚本说明:
            根据查询条件,以分页拿数据的方式导出主材收款;并上传到文件服务器中,可从任务页面上点击下载即可;
            当该脚本执行出现异常时,将跟新进度为失败并记录到log文件中上传至文件服务器中;

            1.参数说明参数要对应
                1.pid         information.plan的主键;
                2.args        "2018/12/13-2018/12/13"
                3.company     公司:1
            2.脚本传参说明:
                 plan_export_funds_ranking.py         pid         args                company
                 plan_export_funds_ranking.py 929 2018/12/01-2018/12/31 1

            3.注:
                1.结束时间不能大于开始时间31天

    """

    modernFilePath = os.path.realpath(__file__)
    configPath = "%s/%s" % (os.path.dirname(modernFilePath), "config.json")

    config = json.load(open(configPath))
    dbconf = config.get("database")

    # pid = sys.argv[1]
    # args = sys.argv[2]
    # comapny = sys.argv[3]
    pid = 320
    args = "2018/12/01-2018/12/31"
    comapny = "1"

    funds_ranking = funds_ranking(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"),
                                  config.get("media"), config.get("pthost"))
    funds_ranking.main(pid, args, int(comapny))
